package com.edou.base.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

/**
 * 操作Excel工具类
 * @creator     admin
 * @create-time May 13, 2015   5:58:41 PM
 * @version 1.0
 */
public class ExcelUtil {

	private static Log logger = LogFactory.getLog(ExcelUtil.class);
	
	/**
	 * 创建Excel行头
	 * @param titleList
	 * @param sheet
	 * @param headRowNumber
	 * @return
	 */
	public static HSSFRow createHSSFRow(List<String> titleList,
			List<Integer> titleWidthList,
			HSSFWorkbook wb,
			HSSFSheet sheet,
			Integer headRowNumber) {
        // 列头
		HSSFRow row = sheet.createRow(headRowNumber);
		if (titleList != null && titleList.size() > 0 ){
			for (int i = 0; i < titleList.size(); i++) {
				sheet.setColumnWidth(i, titleWidthList.get(i) * 256);
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(titleList.get(i));

				HSSFCellStyle cellStyle = wb.createCellStyle();
				// 指定单元格居中对齐
				cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
				// 指定单元格垂直居中对齐
				cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				// 指定单元格自动换行
				cellStyle.setWrapText(true);

				// 设置单元格字体
				HSSFFont font = wb.createFont();
				font.setBoldweight(Font.BOLDWEIGHT_BOLD);
				cellStyle.setFont(font);

				// 设置单元格格式
				cell.setCellStyle(cellStyle);
			}
		}
		return row;
	}
	
	/**
	 * 创建Excel行头
	 * @param titles
	 * @param sheet
	 * @param headRowNumber
	 * @return
	 */
	public static HSSFRow createHSSFRow(String[] titles,
			Integer[] titleWidths,
			HSSFWorkbook wb,
			HSSFSheet sheet,
			Integer headRowNumber) {
        // 列头
		HSSFRow row = sheet.createRow(headRowNumber);
		if (titles != null && titles.length > 0 ){
			for (int i = 0; i < titles.length; i++) {
				sheet.setColumnWidth(i, titleWidths[i] * 256);
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(titles[i]);
				
				HSSFCellStyle cellStyle = wb.createCellStyle();
				// 指定单元格居中对齐
				cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
				// 指定单元格垂直居中对齐
				cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				// 指定单元格自动换行
				cellStyle.setWrapText(true);

				// 设置单元格字体
				HSSFFont font = wb.createFont();
				font.setBoldweight(Font.BOLDWEIGHT_BOLD);
				cellStyle.setFont(font);

				// 设置单元格格式
				cell.setCellStyle(cellStyle);
			}
		}
		return row;
	}
	
	/**
	 * 创建Excel行头
	 * @param titles
	 * @param sheet
	 * @param headRowNumber
	 * @return
	 */
	public static HSSFRow createHSSFRowOverRide(String[] titles,
			Integer[] titleWidths,
			HSSFWorkbook wb,
			HSSFSheet sheet,
			Integer headRowNumber,Integer begin) {
        // 列头
		HSSFRow row = sheet.createRow(headRowNumber);
		if (titles != null && titles.length > 0 ){
			for (int i = begin; i < titles.length; i++) {
				sheet.setColumnWidth(i, titleWidths[i] * 256);
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(titles[i]);
				HSSFCellStyle cellStyle = wb.createCellStyle();
				// 指定单元格居中对齐
				cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
				// 指定单元格垂直居中对齐
				cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				// 指定单元格自动换行
				cellStyle.setWrapText(true);
				
				if(StringUtils.isNotBlank(titles[i])){
					cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
					cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
					//设置边框
					cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				}
				
				// 设置单元格字体
				HSSFFont font = wb.createFont();
				font.setBoldweight(Font.BOLDWEIGHT_BOLD);
				cellStyle.setFont(font);

				// 设置单元格格式
				cell.setCellStyle(cellStyle);
			}
		}
		return row;
	}
	public static HSSFCell createCell(HSSFCellStyle cellStyle,HSSFRow row,int cellNum){
		HSSFCell cell = row.createCell(cellNum);
		cell.setCellStyle(cellStyle);
		return cell;
	}
	
	/**
	 * 导出excel文件
	 * @param excelFileName
	 * @param request
	 * @param response
	 * @param wb
	 * @throws Exception
	 */
	public static void exportExcelData(String excelFileName,HttpServletRequest request,HttpServletResponse response,HSSFWorkbook wb){
		String fileName = new Date().getTime() + "";
		//获取文件名
		fileName = excelFileName + DateUtil.formatDate(new Date(), "yyyyMMdd") + ".xls";
		
		try{
			String agent = request.getHeader("User-Agent");
			if (agent.toUpperCase().indexOf("IE") > 0 || ( null != agent && -1 != agent.indexOf("like Gecko"))) {
				//IE
				fileName = URLEncoder.encode(fileName, "UTF-8");
			}else{
				//other
				fileName = new String(fileName.getBytes(), "ISO-8859-1");
			}
		}catch(Exception ex){
			//转码出现问题
			logger.error("",ex);
			fileName = DateUtil.formatDate(new Date(), "yyyyMMdd") + ".xls";
		}
		
		
		OutputStream os = null;
		try {
			// 清空输出流
			response.reset();
			// 取得输出流
			os = response.getOutputStream(); 
			// 设定输出文件头
			response.setHeader("Content-disposition","attachment; filename=" + fileName);
			// 定义输出类型
			response.setContentType("application/msexcel");
		} catch (Exception ex) {
			// 捕捉异常   
			logger.error("导出" + excelFileName + "发生异常:" + ex.getMessage(),ex);
		}
		try {
			os.flush();
			wb.write(os);
		} catch (Exception e) {
			logger.error("导出" + excelFileName + "发生异常:" + e.getMessage(),e);
		}
	}
	
	
	public static String exportAjaxExcelData(String excelFileName,HSSFWorkbook wb) {
		FileInputStream fis = null;
		FileOutputStream fos = null;
		String excelFilePath="";
		//输出文件
		String tmpZipDirectoryName = UUIDUtil.uuid().toLowerCase();
		String tmpZipDirectoryPath = System.getProperty("java.io.tmpdir") + File.separator + tmpZipDirectoryName + File.separator;
		File tmpZipDirectory = new File(tmpZipDirectoryPath);
		if (!tmpZipDirectory.exists()) {
			tmpZipDirectory.mkdirs();
		}
		try{
			excelFilePath = tmpZipDirectoryPath + tmpZipDirectoryName + ".xls";
			fos = new FileOutputStream(excelFilePath);
			wb.write(fos);
		} catch (Exception e) {
			
		} finally{
			IOUtils.closeQuietly(fis);
			IOUtils.closeQuietly(fos);
		}
		return excelFilePath;
	}
	
	
	public static HSSFCellStyle createHSSFCellStyle(HSSFWorkbook wb){
		HSSFCellStyle cellStyles = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyles.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyles.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定单元格自动换行
		cellStyles.setWrapText(true);
		//设置边框
		cellStyles.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyles.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyles.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyles.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// 设置单元格字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		cellStyles.setFont(font);
		return cellStyles;
	}
	
	public static HSSFCellStyle createHSSFLeftCellStyle(HSSFWorkbook wb){
		HSSFCellStyle cellStyles = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyles.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		// 指定单元格垂直居中对齐
		cellStyles.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定单元格自动换行
		cellStyles.setWrapText(true);
		//设置边框
		cellStyles.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyles.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyles.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyles.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// 设置单元格字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		font.setFontHeightInPoints((short)11);
		cellStyles.setFont(font);
		return cellStyles;
	}
	
	
	public static String getOptionGroups(String optionText){
		List<String> result = new ArrayList<String>();
		Pattern p = Pattern.compile("[(][A-Za-z][)]");
		Matcher m = p.matcher(optionText);
		while(m.find()){
			int start = m.start();
			int end = m.end();
			result.add(optionText.substring(start,end));
		}
		for (String key : result) {
			optionText = optionText.replace(key, "（" + key.substring(1,2) + "）");
		}
		return optionText;
	}
	
	public static float getFloat(String str){
		str = str.replace("（", "").replace("）", "");
		float num = 0;
		if(str.indexOf(".") > 0){
     	   String str1 = str.substring(0, str.indexOf(".") + 1);
     	   String str2 = str.substring(str1.length());
     	   if(StringUtils.isNotBlank(str2)){
     		  str2 = str2.replace(".", "");
     	   }
     	   str1 += str2; 
     	   num = Float.parseFloat(str1);
        }else{
           num = Float.parseFloat(str);
        }
		return num;
	}
}
